package com.lanyou.esb.service;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import com.lanyou.cook.common.Constants;
import com.lanyou.esb.common.CommonUtils;
import com.lanyou.esb.datadictionary.PeriodType;
import com.lanyou.esb.repository.IfResTimeRepository;
import com.lanyou.esb.vo.IfResTimeVo;
import com.lanyou.esb.vo.IfResTimeVo.IfGroup;
import com.lanyou.esb.web.param.IfResTimeParam;

/**
 * 服务响应时间服务
 * 
 * @author Davey.wu
 */
@Service
public class IfResTimeService {
	@PersistenceContext
	private EntityManager entityManager;
	@Autowired
	private IfResTimeRepository ifResTimeRepository;
	private static FastDateFormat FASTDATEFORMAT_YYYYMMDDHHMMSS = FastDateFormat
			.getInstance(Constants.DATETIME_PATTERN_1);
	private static FastDateFormat FASTDATEFORMAT_YYYYMMDD = FastDateFormat
			.getInstance(Constants.DATETIME_PATTERN_YYYYMMDD);

	private static FastDateFormat FASTDATEFORMAT_MM = FastDateFormat
			.getInstance(Constants.DATETIME_PATTERN_MM);

	/**
	 * 查找服务响应时间信息
	 * 
	 * @param param
	 *            ：查询参数
	 * @param pageable
	 *            ：分页对象
	 * @return
	 */
	public IfResTimeVo findIfResTimeInfo(IfResTimeParam param, Pageable pageable) {
		// 当前时间
		Date now = new Date();
		Calendar start = Calendar.getInstance();
		start.setTime(now);
		start.set(Calendar.HOUR_OF_DAY, 0);
		start.set(Calendar.MINUTE, 0);
		start.set(Calendar.SECOND, 0);
		start.set(Calendar.MILLISECOND, 0);

		String startDate = "";
		String endDate = "";
		BigInteger total = null;
		List<Object[]> objects = null;
		// 选择今天
		if (PeriodType.TODAY.ValueCode.equals(param.getPeriod())) {
			Calendar todayC = Calendar.getInstance();
			todayC.setTime(now);
			// 开始时间为：0点0分0秒
			todayC.set(Calendar.HOUR_OF_DAY, 0);
			todayC.set(Calendar.MINUTE, 0);
			todayC.set(Calendar.SECOND, 0);
			todayC.set(Calendar.MILLISECOND, 0);
			startDate = FASTDATEFORMAT_YYYYMMDDHHMMSS.format(todayC);
			// 结束时间为：23点59分59秒
			todayC.set(Calendar.HOUR_OF_DAY, 23);
			todayC.set(Calendar.MINUTE, 59);
			todayC.set(Calendar.SECOND, 59);
			endDate = FASTDATEFORMAT_YYYYMMDDHHMMSS.format(todayC);
			String table = getTable(todayC.getTime());
			// 获取符合条件的总记录数
			total = getServiceResTime4Total(table, param.getIfName(),
					param.getFieldId(), param.getSystemId(), startDate, endDate);
			// 存在符合条件的记录，则取记录
			if (total != null && total.longValue() > 0) {
				objects = getServiceResTime(table, param.getIfName(),
						param.getFieldId(), param.getSystemId(), startDate,
						endDate,
						pageable.getPageNumber() * pageable.getPageSize(),
						pageable.getPageSize());
			}
		}
		// 选择昨天
		if (PeriodType.YESTODAY.ValueCode.equals(param.getPeriod())) {
			Calendar yestodayC = Calendar.getInstance();
			yestodayC.setTime(now);
			yestodayC.add(Calendar.DAY_OF_MONTH, -1);
			startDate = FASTDATEFORMAT_YYYYMMDD.format(yestodayC);

			// 查看总记录数
			List<Object> totals = ifResTimeRepository.findIfResTimeTotal(
					param.getIfName(), param.getFieldId(), param.getSystemId(),
					startDate);
			total = null;
			if (totals != null && totals.size() > 0) {
				total = (BigInteger) totals.get(0);
			}
			// 存在符合条件的记录，则取记录
			if (total != null && total.longValue() > 0) {
				objects = ifResTimeRepository.findIfResTime(param.getIfName(),
						param.getFieldId(), param.getSystemId(), startDate,
						pageable.getPageNumber() * pageable.getPageSize(),
						pageable.getPageSize());
			}
		}
		// 选择7天内
		if (PeriodType.WEEK.ValueCode.equals(param.getPeriod())) {
			Calendar sevenbeforeC = Calendar.getInstance();
			sevenbeforeC.setTime(now);
			// 设置开始时间为7天前
			sevenbeforeC.add(Calendar.DAY_OF_MONTH, -7);
			startDate = FASTDATEFORMAT_YYYYMMDD.format(sevenbeforeC);
			// 结果时间为开始时间往后6天
			sevenbeforeC.add(Calendar.DAY_OF_MONTH, 6);
			endDate = FASTDATEFORMAT_YYYYMMDD.format(sevenbeforeC);

			// 查看总记录数
			List<Object> totals = ifResTimeRepository.findIfResTimeTotal(
					param.getIfName(), param.getFieldId(), param.getSystemId(),
					startDate, endDate);
			total = null;
			if (totals != null && totals.size() > 0) {
				total = (BigInteger) totals.get(0);
			}
			// 存在符合条件的记录，则取记录
			if (total != null && total.longValue() > 0) {
				objects = ifResTimeRepository.findIfResTime(param.getIfName(),
						param.getFieldId(), param.getSystemId(), startDate,
						endDate,
						pageable.getPageNumber() * pageable.getPageSize(),
						pageable.getPageSize());
			}
		}
		IfResTimeVo vo = new IfResTimeVo();
		// 无记录，返回空集
		if (total == null || total.longValue() <= 0) {
			vo.setTotal(0l);
			vo.setIfGroups(new ArrayList<>(0));
			return vo;
		}
		// 设置总记录数
		vo.setTotal(total.longValue());
		List<IfResTimeVo.IfGroup> ifGroups = new ArrayList<>(objects.size());
		vo.setIfGroups(ifGroups);
		// 转换结果集
		for (Object[] object : objects) {
			String fieldName = object[0] != null ? (String) object[0] : "";
			String systemName = object[1] != null ? (String) object[1] : "";
			String ifName = object[2] != null ? (String) object[2] : "";
			double avg = object[3] != null ? ((BigDecimal) object[3])
					.doubleValue() : 0;
			double max = object[4] != null ? ((BigDecimal) object[4])
					.doubleValue() : 0;
			double esbAvg = object[5] != null ? ((BigDecimal) object[5])
					.doubleValue() : 0;
			double esbMax = object[6] != null ? ((BigDecimal) object[6])
					.doubleValue() : 0;

			IfResTimeVo.IfGroup ifGroup = IfGroup.getInstance(fieldName,
					systemName, ifName);
			ifGroup.setPeriod(param.getPeriod());
			ifGroup.setAvg(avg);
			ifGroup.setMax(max);
			ifGroup.setEsbAvg(esbAvg);
			ifGroup.setEsbMax(esbMax);
			ifGroups.add(ifGroup);
		}
		return vo;
	}

	/**
	 * 获取服务平均响应时间总记录<br>
	 * 描述：只查某一天的记录，天数较多或跨月查询会有性能问题
	 * 
	 * @param table
	 *            ：表名
	 * @param ifName
	 *            ：接口名称
	 * @param fieldId
	 *            ：领域标识
	 * @param systemId
	 *            ：系统标识
	 * @param startDate
	 *            ：开始时间
	 * @param endDate
	 *            ：结束时间
	 * @param pageNumber
	 *            ：页码
	 * @param pageSize
	 *            ：页大小
	 * @return
	 */
	private List<Object[]> getServiceResTime(String table, String ifName,
			String fieldId, String systemId, String startDate, String endDate,
			long pageNumber, long pageSize) {
		String sql = "SELECT  "
				+ "(SELECT f0.FIELD_NAME FROM t_esb_bu_if_http h0,t_cook_bu_field_system s0,t_cook_db_field f0 WHERE h0.IF_HTTP_ID=m.IF_ID AND h0.FIELD_SYSTEM_ID=s0.FIELD_SYSTEM_ID AND s0.FIELD_ID=f0.FIELD_ID) AS fieldName, "
				+ "(SELECT s0.SYSTEM_NAME FROM t_esb_bu_if_http h0,t_cook_bu_field_system s0 WHERE h0.IF_HTTP_ID=m.IF_ID AND h0.FIELD_SYSTEM_ID=s0.FIELD_SYSTEM_ID) AS fsName, "
				+ "(SELECT h0.IF_NAME FROM t_esb_bu_if_http h0 WHERE h0.IF_HTTP_ID=m.IF_ID) AS ifName, "
				+ "AVG(m.TOTAL_RESPONSE_TIMES) AS avgResTime, "
				+ "MAX(m.TOTAL_RESPONSE_TIMES) AS maxResTime,  "
				+ "AVG(m.PLATFORM_TIMES) AS esbAvgResTime, "
				+ "MAX(m.PLATFORM_TIMES) AS esbMaxResTime  " + "FROM " + table
				+ "  m  " + "WHERE     m.IF_ID IS NOT NULL ";
		// 添加接口名称条件
		if (StringUtils.isNotBlank(ifName)) {
			sql += "AND (m.IF_NAME LIKE :ifName) ";
		}
		// 添加领域标识条件
		if (StringUtils.isNotBlank(fieldId)) {
			sql += "AND (m.FIELD_ID =:fieldId) ";
		}
		// 添加系统标识条件
		if (StringUtils.isNotBlank(systemId)) {
			sql += "AND (m.FIELD_SYSTEM_ID =:systemId)";
		}

		sql += "AND m.IN_ESB_DATE>=:startDate AND m.IN_ESB_DATE<=:endDate GROUP BY m.IF_ID LIMIT :pageNumber,:pageSize";
		Query query = entityManager.createNativeQuery(sql);

		// 添加接口名称参数
		if (StringUtils.isNotBlank(ifName)) {
			query.setParameter("ifName", ifName);
		}
		// 添加领域标识参数
		if (StringUtils.isNotBlank(fieldId)) {
			query.setParameter("fieldId", fieldId);
		}
		// 添加系统标识参数
		if (StringUtils.isNotBlank(systemId)) {
			query.setParameter("systemId", systemId);
		}

		query.setParameter("startDate", startDate);
		query.setParameter("endDate", endDate);
		query.setParameter("pageNumber", pageNumber);
		query.setParameter("pageSize", pageSize);
		// 获取结果集
		return  query.getResultList();
	}

	/**
	 * 获取服务平均响应时间总记录数 <br>
	 * 描述：只查某一天的记录，天数较多或跨月查询会有性能问题
	 * 
	 * @param table
	 *            ：表名
	 * @param ifName
	 *            ：接口名称
	 * @param fieldId
	 *            ：领域标识
	 * @param systemId
	 *            ：系统标识
	 * @param startDate
	 *            ：开始时间
	 * @param endDate
	 *            ：结束时间
	 * @return
	 */
	private BigInteger getServiceResTime4Total(String table, String ifName,
			String fieldId, String systemId, String startDate, String endDate) {
		String sql = "SELECT COUNT(V0.IF_ID) AS total FROM 	(SELECT  DISTINCT m.IF_ID FROM "
				+ table + " m " + "WHERE     m.IF_ID IS NOT NULL ";
		// 添加接口名称条件
		if (StringUtils.isNotBlank(ifName)) {
			sql += "AND (m.IF_NAME LIKE :ifName) ";
		}
		// 添加领域标识参数
		if (StringUtils.isNotBlank(fieldId)) {
			sql += "AND (m.FIELD_ID =:fieldId) ";
		}
		// 添加系统标识条件
		if (StringUtils.isNotBlank(systemId)) {
			sql += "AND (m.FIELD_SYSTEM_ID =:systemId)";
		}
		sql += "AND m.IN_ESB_DATE>=:startDate AND m.IN_ESB_DATE<=:endDate ) AS V0 ";
		Query query = entityManager.createNativeQuery(sql);
		// 添加接口名称参数
		if (StringUtils.isNotBlank(ifName)) {
			query.setParameter("ifName", ifName);
		}
		// 添加领域标识参数
		if (StringUtils.isNotBlank(fieldId)) {
			query.setParameter("fieldId", fieldId);
		}
		// 添加系统标识参数
		if (StringUtils.isNotBlank(systemId)) {
			query.setParameter("systemId", systemId);
		}

		query.setParameter("startDate", startDate);
		query.setParameter("endDate", endDate);
		// 获取记录
		List rows = query.getResultList();
		if (rows == null || rows.isEmpty()) {
			return new BigInteger("0");
		}
		return (BigInteger) rows.get(0);
	}

	/**
	 * 统计及更新服务响应时间<br>
	 * 
	 * 描述：每天凌晨0点执行统计
	 * 
	 * @param start
	 *            ：开始时间
	 * @param end
	 *            ：结束时间
	 */
	@Scheduled(cron = "0 0 0 * * ?")
	@Transactional
	public void statistics() {
		Date now = new Date();
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(now);
		calendar.add(Calendar.DAY_OF_MONTH, -1);
		now = calendar.getTime();
		// 删除7天前的数据
		delSevenBeforeDayData(now);
		// 统计更新
		statIfResTimeByDay(now);
	}

	/**
	 * 删除7天前的服务响应时间数据
	 * 
	 * @param date
	 */
	public void delSevenBeforeDayData(Date date) {
		String sevenBeforeDate = "";
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		// 设置时间为：7天前的0点0分0秒
		calendar.add(Calendar.DAY_OF_MONTH, -7);
		calendar.set(Calendar.HOUR_OF_DAY, 0);
		calendar.set(Calendar.MINUTE, 0);
		calendar.set(Calendar.SECOND, 0);
		sevenBeforeDate = FASTDATEFORMAT_YYYYMMDDHHMMSS.format(calendar);
		String sql = createSql4DelSevenBeforeData();
		Query query = entityManager.createNativeQuery(sql);
		query.setParameter("sevenBeforeDate", sevenBeforeDate);
		// 统计更新
		query.executeUpdate();
		entityManager.flush();
	}

	/**
	 * 统计某一天的服务响应时间数据
	 * 
	 * @param date
	 *            ：时期
	 */
	private void statIfResTimeByDay(Date date) {
		String startDate = "";
		String endDate = "";
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		// 设置开始时间为0点0分0秒
		calendar.set(Calendar.HOUR_OF_DAY, 0);
		calendar.set(Calendar.MINUTE, 0);
		calendar.set(Calendar.SECOND, 0);
		startDate = FASTDATEFORMAT_YYYYMMDDHHMMSS.format(calendar);
		// 设置结束时间为23点59分59秒
		calendar.set(Calendar.HOUR_OF_DAY, 23);
		calendar.set(Calendar.MINUTE, 59);
		calendar.set(Calendar.SECOND, 59);
		endDate = FASTDATEFORMAT_YYYYMMDDHHMMSS.format(calendar);
		// 获取表
		String table = getTable(date);
		// 生成SQL
		String sql = createSql4Statistic(table);
		Query query = entityManager.createNativeQuery(sql);
		query.setParameter("startDate", startDate);
		query.setParameter("endDate", endDate);
		// 统计更新
		query.executeUpdate();
	}

	/**
	 * 删除服务响应时间过期数据SQL
	 * 
	 * @return
	 */
	private String createSql4DelSevenBeforeData() {
		String sql = "DELETE FROM T_ESB_BU_IF_RES_TIME  WHERE STATISTICS_DATE < :sevenBeforeDate";
		return sql;
	}

	/**
	 * 统计某一天服务响应时间SQL
	 * 
	 * @param table
	 * @return
	 */
	private String createSql4Statistic(String table) {
		String sql = "INSERT INTO T_ESB_BU_IF_RES_TIME SELECT "
				+ "V1.FIELD_ID, "
				+ "V1.FIELD_SYSTEM_ID, "
				+ "V1.IF_ID, "
				+ ":startDate  AS STATISTICS_DATE, "
				+ "FORMAT(V1.AVG_TIME,3), "
				+ "FORMAT(V1.MAX_TIME,3), "
				+ "FORMAT(V1.ESB_AVG_TIME,3), "
				+ "FORMAT(V1.ESB_MAX_TIME,3), "
				+ "V1.IS_ENABLE, "
				+ "V1.CREATOR, "
				+ "V1.CREATED_DATE, "
				+ "V1.MODIFIER, "
				+ "V1.LAST_UPDATED_DATE  "
				+ " FROM "
				+ "	(SELECT  "
				+ "	(SELECT F.FIELD_ID FROM t_esb_bu_if_http H,t_cook_bu_field_system F  WHERE H.IF_HTTP_ID=V0.IF_ID AND H.FIELD_SYSTEM_ID=F.FIELD_SYSTEM_ID) AS FIELD_ID, "
				+ "	(SELECT H.FIELD_SYSTEM_ID FROM t_esb_bu_if_http H WHERE H.IF_HTTP_ID=V0.IF_ID) AS FIELD_SYSTEM_ID, "
				+ "	V0.IF_ID, "
				+ "	V0.astaticsDate AS STATISTICS_DATE, "
				+ "	V0.sumResTimes/V0.countIf AS AVG_TIME, "
				+ "	V0.maxResTimes AS MAX_TIME, "
				+ "	V0.sumEsbResTimes/V0.countIf AS ESB_AVG_TIME, "
				+ "	V0.maxEsbResTimes AS ESB_MAX_TIME, "
				+ "	V0.IS_ENABLE AS IS_ENABLE, "
				+ "	V0.CREATOR AS CREATOR, "
				+ "	V0.CREATED_DATE AS CREATED_DATE, "
				+ "	V0.MODIFIER AS MODIFIER, "
				+ "	V0.LAST_UPDATED_DATE AS LAST_UPDATED_DATE  "
				+ "	FROM "
				+ "		(SELECT m.IF_ID, "
				+ "		COUNT(m.IF_ID) AS countIf, "
				+ "		SUM(m.TOTAL_RESPONSE_TIMES) AS sumResTimes, "
				+ "		MAX(m.TOTAL_RESPONSE_TIMES) AS maxResTimes, "
				+ "		SUM(m.PLATFORM_TIMES) AS sumEsbResTimes, "
				+ "		MAX(m.PLATFORM_TIMES) AS maxEsbResTimes, "
				+ "		m.IN_ESB_DATE AS astaticsDate, "
				+ "		m.IS_ENABLE, "
				+ "		m.CREATOR, "
				+ "		m.CREATED_DATE, "
				+ "		m.MODIFIER, "
				+ "		m.LAST_UPDATED_DATE  "
				+ "		FROM "
				+ table
				+ " m  "
				+ "		WHERE  m.IN_ESB_DATE>=:startDate AND m.IN_ESB_DATE<=:endDate AND m.IF_ID IS NOT NULL "
				+ "		GROUP BY m.IF_ID "
				+ "		) AS V0 "
				+ "	)AS V1 	"
				+ " WHERE V1.FIELD_ID IS NOT NULL "
				+ " AND V1.FIELD_SYSTEM_ID IS NOT NULL "
				+ " AND NOT EXISTS(SELECT t.FIELD_ID FROM T_ESB_BU_IF_RES_TIME t "
				+ "     WHERE t.FIELD_ID=V1.FIELD_ID "
				+ "     AND t.FIELD_SYSTEM_ID=V1.FIELD_SYSTEM_ID "
				+ "     AND t.IF_ID=V1.IF_ID "
				+ "     AND t.STATISTICS_DATE=:startDate ) ";

		return sql;
	}

	/**
	 * 根据日期获取日志表名
	 * 
	 * @param start
	 * @return
	 */
	private String getTable(Date start) {
		return CommonUtils.ACCESS_LOG_TABLE_NAME
				+ FASTDATEFORMAT_MM.format(start);
	}
}
